package db;
import model.*;
import java.util.Date;

import java.sql.*;
import java.util.ArrayList;

/**
 * DBWorkOutProgram.java
 *
 */
public class DBWorkOutProgram {
	private  Connection con;
	/** Creates a new instance 
	 * of DBWorkOutProgram */
	public DBWorkOutProgram() {
		con = DBConnection.getInstance().getDBcon();
	}
	

	// get all WorkOutPrograms
	public ArrayList<WorkOutProgram> getWorkOutPrograms(boolean retriveAssociation)
	{
		return miscWhere("", retriveAssociation);
	}

	//find user's WorkOutPrograms

	public ArrayList<WorkOutProgram> findUserWorkOutPrograms(String userId, boolean retriveAssociation)
	{   String wClause = "  userId = '" + userId + "'";
	return miscWhere(wClause, retriveAssociation);
	}

	//get one WorkOutProgram with the id
	public WorkOutProgram findWorkOutProgram(String id, boolean retriveAssociation)
	{   String wClause = "  id = '" + id + "'";
	return singleWhere(wClause, retriveAssociation);
	}

	
	//insert a new WorkOutProgram
	public int insertWorkOutProgram(boolean isCompleted, String date, String userId)
	{     
		int id = GetMax.getMaxId("Select max(id) From WorkOutProgram");
		id += 1;
		
		
		String query="INSERT INTO WorkOutProgram(id,date,isCompleted,userId )  " +
		"VALUES("+
		id					+ ",'"  +
		date				+ "','"  +
		"false"				+ "','"  +
		userId				+"')";   

		System.out.println("insert : " + query);		  					
		try{ // insert new WorkOutProgram
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeUpdate(query);
			stmt.close();
		}//end try	
		catch(Exception ex){
			System.out.println("Insert exception in WorkOutProgram db: "+ex);
		}
		return(id);
	}

	public int updateWorkOutProgram(WorkOutProgram program,User user)
	{

		WorkOutProgram programObj  = program;
		int rc=-1;

		String query="UPDATE WorkOutProgram SET "+
		"date =" 		+ programObj.getDate() 			+","+
		"isCompleted =" + programObj.getIsCompleted() 	+","+
		"userId ='" 	+ user.getUserId() 				+"'"+
		" WHERE id = '" +programObj.getId()				+"'";
		System.out.println("Update query:" + query);
		try{ // update WorkOutProgram
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		}//slut try	
		catch(Exception ex){
			System.out.println("Update exception in WorkOutProgram db: "+ex);
		}
		return(rc);
	}
	
	
	public void addToProgram(int workOutProgramid,int workOutId){
		String query="INSERT INTO WorkOutPrograms(workOutProgramid,workOutId) " +
		"VALUES("+
		workOutProgramid			+ ","+
		workOutId					+")";   
		
		try{ // insert new WorkOutProgram
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeUpdate(query);
			stmt.close();
		}//end try	
		catch(Exception ex){
			System.out.println("Insert exception in WorkOutProgram db: "+ex);
		}
		
	}
	
	
	public int deleteWorkOutProgram(String id)
	{
		int rc=-1;

		String query="DELETE FROM WorkOutProgram WHERE id = '" +
		id + "'";
		System.out.println(query);
		try{ // delete from WorkOutProgram
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//slut try	
		catch(Exception ex){
			System.out.println("Delete exception in WorkOutProgram db: "+ex);
		}
		return(rc);
	}
	
	public ResultSet getUserWorkOutPrograms(String userId){
		ResultSet results = null;

		String query ="Select id,date,isCompleted "+
		"From WorkOutProgram " +
		"Where userId='" +userId +"'";
		System.out.println(query);

		try{ // read the WorkOut from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);



		}
		catch(Exception e) {
			System.out.println("Exception in getUserWorkOutPrograms: " +e);

		}


		return results;

	}

	

	//private methods
	//michWere is used whenever we want to select more than one WorkOutProgram


	private ArrayList<WorkOutProgram> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<WorkOutProgram> list = new ArrayList<WorkOutProgram>();	

		String query =  buildQuery(wClause);

		try{ // read the WorkOutProgram from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			//int snr=0;
			while( results.next() ){
				WorkOutProgram programObj = new WorkOutProgram();
				programObj = buildProgram(results);	
				list.add(programObj);	
			}//end while
			// if retrieveAssociation is true, the supervisor and department
			// has to be selected for each object in the list
			stmt.close();
		}//slut try	
		catch(Exception e){
			System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}
		return list;
	}

	//Singelwhere is used when we only select one WorkOutProgram 	
	private WorkOutProgram singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		WorkOutProgram programObj = new WorkOutProgram();
		//programObj.setId();
		String query =  buildQuery(wClause);
		System.out.println(query);
		try{ // read the WorkOutProgram from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if( results.next() ){
				programObj = buildProgram(results);


			}

		}//end try	
		catch(Exception e){
			System.out.println("Query exception: "+e);
		}
		return programObj;
	}
	//method to build the query
	private String buildQuery(String wClause)
	{
		String query="SELECT * FROM WorkOutProgram";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}
	//method to build an WorkOutProgram object
	private WorkOutProgram buildProgram(ResultSet results)
	{   WorkOutProgram programObj = new WorkOutProgram();
	//User userObj = new User();
	try{
		programObj.setId(results.getInt(1));
		programObj.setDate(results.getString(2)); 
		programObj.setIsCompleted(results.getBoolean(3));     	  

	}
	catch(Exception e)
	{
		System.out.println("error in building the WorkOutProgram object");
	}
	return programObj;
	}


}  


